package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import util.Convert;


import TouristManagement.CoordsGPS;
import TouristManagement.Hours;

public class SiteDAO {

private MySqlConnecte myConn = new  MySqlConnecte();
		
	//Verifier visite: true si déjà visité, false si non 
	public  List<Short> getIdSite(){
		try{
			final String queryOrdre = "SELECT idSite FROM site";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			final ResultSet res = preparedQuery.executeQuery();
			List<Short> listId= new ArrayList<Short>();
			
			while (res.next()){
				listId.add((short)res.getInt(1));
			}
			return listId;
		
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}
	
	//getHoraireOuverture du site
	public  Hours getHoraireOuverture(int idSite){
		try{
			final String queryOrdre = "SELECT horaireIn FROM site WHERE idSite=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			Time tm = res.getTime(1);
			Hours hr = Convert.timeToHours(tm);
			return hr;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}
	
	//get HoraireFerme
	public  Hours getHoraireFermeture(int idSite){
		try{
			final String queryOrdre = "SELECT horaireOut FROM site WHERE idSite=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			Time tm = res.getTime(1);
			Hours hr = Convert.timeToHours(tm);
			return hr;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}

	//getNbVisiteurMaximal
	public  short getNbVisiteurMaximal(int idSite){
		try{
			final String queryOrdre = "SELECT nbVisiteurMax FROM site WHERE idSite=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int resul = res.getInt(1);
			return (short)resul;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
	
	//get getNbVisiteurActuel
	public  short getNbVisiteurActuel(int idSite){
		try{
			final String queryOrdre = "SELECT count(*) FROM visite WHERE  idSite=? and dateIN is not null and dateOUT is null";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int resul = res.getInt(1);
			return (short)resul;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
	
	//get durée visite maximum d'un site(en minutes) 
	public  short getdurationVisitMax(int idSite){
		try{
			final String queryOrdre = "SELECT max(minute(timediff(dateIN,dateOUT))) FROM visite WHERE idSite=? and dateIN is not null AND dateOUT is not null;";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int resul = res.getInt(1);
			return (short)resul;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}
	
	//get durée visite minimum d'un site(en minutes) 
	public  short getdurationVisitMin(int idSite){
		try{
			final String queryOrdre = "SELECT min(minute(timediff(dateIN,dateOUT))) FROM visite WHERE idSite=? and dateIN is not null AND dateOUT is not null;";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int resul = res.getInt(1);
			return (short)resul;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}

	//get durée visite moyen d'un site(en minutes) 
	public  short getdurationVisitMoyen(int idSite){
		try{
			final String queryOrdre = "SELECT avg(minute(timediff(dateIN,dateOUT))) FROM visite WHERE idSite=? and dateIN is not null AND dateOUT is not null;";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int resul = res.getInt(1);
			return (short)resul;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return -1;
		}		
	}

	//getC coordonne GPS d'un site
	public  CoordsGPS getCoordGPS(int idSite){
		try{
			final String queryOrdre = "SELECT latitude,longtitude FROM site WHERE idSite=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			int lat = res.getInt(1);
			int lon = res.getInt(2);
			CoordsGPS gps = new CoordsGPS((short)lat,(short)lon);
			return gps;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}
	
	//get nom du Site
	public  String getNameSite(int idSite){
		try{
			final String queryOrdre = "SELECT nomSite FROM site WHERE idSite=?";
			PreparedStatement preparedQuery = this.myConn.getConnection().prepareStatement(queryOrdre);
			preparedQuery.setInt(1, idSite);
			final ResultSet res = preparedQuery.executeQuery();
			res.first();
			String str= res.getString(1);
			return str;
		}
		catch(Exception e){
			e.printStackTrace(System.err);
			return null;
		}		
	}
	
//	public static void main(String[] args) {
//		// TODO Auto-generated method stub
//		SiteDAO sD = new SiteDAO();
//		List<Short> listId = new ArrayList<Short>();
//		listId=sD.getIdSite();
//		for(int i=0;i< listId.size();i++){
//			System.out.print(listId.get(i)+"  ");
//		}
//		System.out.println("");
//		short idSite=1;
//		System.out.println("Information sur le site"+idSite+":");
//		//System.out.println("Heure ouvert: "+Convert.hoursToString(sD.getHoraireOuverture(idSite)));
//		//System.out.println("Heure ferme: "+Convert.hoursToString(sD.getHoraireFermeture(idSite)));
//		System.out.println("Nb visite max :"+sD.getNbVisiteurMaximal(idSite));
//		System.out.println("Nb visite actuel :"+sD.getNbVisiteurActuel(idSite));
//		System.out.println("Visite Max: "+sD.getdurationVisitMax(idSite));
//		System.out.println("Viste Min: "+sD.getdurationVisitMin(idSite));
//		System.out.println("Visite Moy: "+sD.getdurationVisitMoyen(idSite));
//	}

}
